package cn.edu.swu.book;

import cn.edu.swu.database.DBTool;
import cn.edu.swu.database.resultsetVisitor;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class bookRepo extends DBTool {
    private static bookRepo instance =new bookRepo();
    public static bookRepo getInstance() {
        return instance;
    }

    public boolean addBook(BOOK book) throws SQLException {
        String insertTemplate ="insert into book(picture,name,author,content,price) values('%s','%s','%s','%s','%s')";
        String sql =String.format(insertTemplate,book.getPicture(),book.getName(),book.getAuthor(),book.getContent(),book.getPrice());
        return this.execute(sql);
    }

    public boolean deleteBook(String id) throws SQLException {
        String deleteTemplate ="delete from book where id='%s'";
        String sql =String.format(deleteTemplate,id);
        return this.execute(sql);
    }

    public boolean updateBook(BOOK book) throws SQLException {
        String sql=null;
        if(book.getPicture()!=null){
            String template = "update book set " +
                    "picture='%s',name='%s', author='%s', price=%s, content='%s' where id=%s";
            sql = String.format(template,
                    book.getPicture(),book.getName(), book.getAuthor(), book.getPrice(), book.getContent(),
                    book.getId());
        }else {
            String insertTemplate = "update book set name='%s',author='%s',content='%s',price='%s' where id='%s'";
            sql = String.format(insertTemplate, book.getName(), book.getAuthor(), book.getContent(), book.getPrice(), book.getId());
        }
        return this.execute(sql);
    }

    public List<BOOK> searchBook(String type,String key) throws SQLException {
        String template="select * from book where %s like '%%%s%%'";
        //%%->% sql:%内容%
        String sql=String.format(template,type,key);
        return bookRepo.getInstance().queryBook(sql);
    }

    //从数据库中查询，并放到list中
    public List<BOOK> queryBook(String sql) throws SQLException {
        final List<BOOK> books=new ArrayList<>();
        this.query(sql, new resultsetVisitor() {
            @Override
            //匿名类
            public void visit(ResultSet resultSet) {
                BOOK book=new BOOK();
                try{
                    book.setId(resultSet.getInt("id"));
                    book.setPicture(resultSet.getString("picture"));
                    book.setName(resultSet.getString("name"));
                    book.setContent(resultSet.getString("content"));
                    book.setAuthor(resultSet.getString("author"));
                    book.setPrice(resultSet.getFloat("price"));
                    books.add(book);
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
        });
        return books;
    }

    public BOOK getBookById(String id) throws SQLException {
        String template="select * from book where id=%s";
        String sql=String.format(template,id);
        System.out.println(sql);
        List<BOOK> books =this.queryBook(sql);
        return !books.isEmpty() ? books.get(0) : null;
    }

    public int totalBooks() throws SQLException {
        String sql="select count(*) from book";
        final int[] total={0};
        //final->可以识别 int[]->可以修改
        this.query(sql, new resultsetVisitor() {
            @Override
            public void visit(ResultSet resultSet) {
                try{
                    total[0]=resultSet.getInt(1);
                    //只返回一条记录：数量 下标为1
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
        });
        return total[0];
    }

    public int totalBookPages(int pageSize) throws SQLException {
        String template="select ceil(count(*)/%s) from book";
        String sql=String.format(template,pageSize);
        final int[] pages={0};
        //final->可以识别 int[]->可以修改
        this.query(sql, new resultsetVisitor() {
            @Override
            public void visit(ResultSet resultSet) {
                try{
                    pages[0]=resultSet.getInt(1);
                    //只返回一条记录：数量 下标为1
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
        });
        return pages[0];
    }
}
